package tsli.acm.dao;

import java.util.List;
import java.util.Map;

import tsli.acm.database.Database;

public class RententionDAO {
	private Database db;
    

	 public RententionDAO(Database db) {
		 this.db = db;
	 }
	 
	 public List<Map<String, Object>> getAllAgentStructure() {
			String vaSql = "SELECT * "
					+ "FROM TSLI_RENTENTION";
			List<Map<String, Object>> result = db.queryList(vaSql);
	        
	        if (result != null && result.size() > 0) {
	            return result;
	        } else {
	            return null;
	        }
		}
	
	 public String getRetentionRatioByForMM(String paClosYm) {
			String vaSql = "SELECT CAST(NVL(retentions.SUM_RETENTION,0) as DECIMAL (6,2)) AS RETENTION_AMOUNT,CASE "
					    +" WHEN NVL(retentions.SUM_RETENTION,0) < 30 THEN 0.7 "
					    +" WHEN NVL(retentions.SUM_RETENTION,0) BETWEEN 30 AND 39 THEN 0.8 "
					    +" WHEN NVL(retentions.SUM_RETENTION,0) BETWEEN 40 AND 49 THEN 1 "
					    +" WHEN NVL(retentions.SUM_RETENTION,0) >= 60 THEN 1.2 "
					   +" END AS RATIO "
					+" FROM (SELECT (100 *  ( sum( pro4.SUMPro4+ pro7.SUMPro7) /  sum ( LP4.SUMLP4+ LP7.SUMLP7) )) AS SUM_RETENTION "
					  +" FROM (SELECT SUM(BIZRT_ITM_AMT ) AS SUMPro4 "
					         +" FROM TSLI_RENTENTION "
					         +" WHERE BIZRT_ITM_CD in ('S11020A10235') AND CLOS_YM = ?  "
                   +" AND PE_NO IN ( SELECT DISTINCT(PE_NO_MM) "
                                  +" FROM TSLI_SFC_COMMISSION_POSITIVE  "
                                 +" WHERE clos_ym = 201411 AND cont_mms = 1 AND fyp >=2000)) pro4 , "
     
					        +" (SELECT SUM(BIZRT_ITM_AMT ) AS SUMPro7 "
					        +" FROM TSLI_RENTENTION "
					       +"  WHERE BIZRT_ITM_CD in ('S11020A10222') AND CLOS_YM = ? "
					        		 +" AND PE_NO IN ( SELECT DISTINCT(PE_NO_MM) "
					         +" FROM TSLI_SFC_COMMISSION_POSITIVE  "
                                  +" WHERE clos_ym = 201411 AND cont_mms = 1 AND fyp >=2000)) pro7 , "
                                  +" (SELECT SUM(BIZRT_ITM_AMT ) AS SUMLP4 "
					        		 +" FROM TSLI_RENTENTION "
					         +" WHERE BIZRT_ITM_CD in ('S11020A10236') AND CLOS_YM = ? "
					        		 +" AND PE_NO IN ( SELECT DISTINCT(PE_NO_MM) "
					         +" FROM TSLI_SFC_COMMISSION_POSITIVE  "
                                  +"  WHERE clos_ym = 201411 AND cont_mms = 1 AND fyp >=2000)) LP4 , "
                                  +" (SELECT SUM(BIZRT_ITM_AMT ) AS SUMLP7 "
					        		 +" FROM TSLI_RENTENTION "
					         +" WHERE BIZRT_ITM_CD in ('S11020A10223') AND CLOS_YM = ? "
					        		 +" AND PE_NO IN ( SELECT DISTINCT(PE_NO_MM) "
					         +" FROM TSLI_SFC_COMMISSION_POSITIVE "
                                  +" WHERE clos_ym = 201411 AND cont_mms = 1 AND fyp >=2000)) LP7 ) retentions ";
			Map<String, Object> result = db.querySingle(vaSql, paClosYm, paClosYm , paClosYm, paClosYm);
			if (result != null) {
				return (String) result.get("RATIO").toString()+"/"+ result.get("RETENTION_AMOUNT").toString();
			} else {
				return null;
			}
		}

}
